<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="LAFDC.DataAccess" %>
<%@ Import Namespace="LAFDC.Logging" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<script language="c#" runat="server">
	public static string source = "data2csv.aspx";

    protected void Page_Load(object sender, EventArgs e) {
        string sql_query = this.Request.QueryString.Get("sql");
        string nom_fichier = this.Request.QueryString.Get("nom");

        if ((nom_fichier == null) || (nom_fichier.Length == 0)) {
			nom_fichier="data.csv";
        }

        if ((sql_query == null) || (sql_query.Length == 0)) {
			// Empty query string - what do we do?
        }
        else {
            string logFile = "data2csv.txt";
            string logDir = "C:\\program files\\LAFDC\\LAFDCWeb\\Logging";
            string logPath = logDir + "\\" + logFile;

            if (!Directory.Exists(logDir)) {
                Directory.CreateDirectory(logDir);
            }

            // Check if the file exist before trying to write events there;
            if (!File.Exists(logPath)) {
                using (StreamWriter sw = new StreamWriter(logPath)) {
                    sw.Close();
                }
            }

            using (StreamWriter sw = new StreamWriter(logPath, false, Encoding.Default)) {
                sw.Write(ExecuteSql(sql_query));
                sw.Close();
            }

            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=" + nom_fichier);
            Response.ContentType = "text/plain";
            Response.WriteFile(logPath);
            Response.End();
        }
    }

    private string ExecuteSql(string sql) {
        ConnectionManager cn = new ConnectionManager();
        SqlConnection conn = cn.GetSqlConnection();
        SqlCommand tsql = conn.CreateCommand();

        tsql.CommandType = CommandType.Text;
        tsql.CommandText = sql;

        FDCLogger.LogEvent("Preparing to execute SQL statement: " + sql, source, 1);

        conn.Open();
        StringBuilder file = new StringBuilder();
        string[] a_row;

		int c_count = 0;
        using (SqlDataReader r = tsql.ExecuteReader()) {
            bool isFirstRow = true;
            while (r.Read()) {
                if (isFirstRow) {
                    // Generate header
                    // First we get the column count
                    c_count = r.FieldCount;
                    a_row = new string[c_count];
                    // Then we get the column names
                    for (int i = 0; i < c_count; i++) {
                        a_row[i] = "\"" + r.GetName(i) + "\"";
                    }
                    file.AppendLine(string.Join(";", a_row));
                    isFirstRow = false;
                }
                a_row = new string[c_count];
                for (int i = 0; i < c_count; i++) {
                    a_row[i] = "\"" + r.GetValue(i).ToString().Replace("\"", "\"\"") + "\"";
                }
                file.AppendLine(string.Join(";", a_row));
            }
        }
        conn.Close();
        return file.ToString();
    }
</script>
<body></body>
</html>
